MINUS and INTERSECT in MySQL

MySQL doesn’t support the INTERSECT and MINUS set operators. The INTERSECT operator takes the results of two queries and returns only rows that appear in both result sets. The MINUS operator takes the distinct rows of one query and returns the rows that do not appear in a second result set. We can rewrite these queries by using JOIN operator:

Sample query with the MINUS operator:

In MySQL:

Sample query with the INTERSECT operator:

In MySQL:

Please share
  • 8
  •  
  •  
  •  
  •  
  •  

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

37 Comments

  1. Saulo Fonseca

    Why MySQL doen’t have these commands? They are also no presente in others SQL systems?

    • Gokhan Atil

      Not all the databases support these SET commands. If users ask for these features, maybe MySQL developers add these features to the TODO list 🙂

  2. Sandhikshan

    I have 3 tables in MySQL. I have a result-set by joining the first 2. I have to find the difference between the resultset that I got and the table 3 that I have. In Oracle I could achieve this by following query:
    select acm.asoc_id, cc.comm_id from asoc_comp_map acm, comm_comp cc where acm.competency=cc.competency
    minus
    (select ac.asoc_id, ac.comm_id from asoc_comm ac);

    How to do the same in MySqL?

    • Gokhan Atil

      You need to join the tables in a subquery:

      SELECT j.* FROM
      (SELECT customer_name, phone_number
      FROM customer, phone
      WHERE customer.phone_id = phone.phone_id) j

      LEFT JOIN filtered f
      ON j.customer_name = f.customer_name
      AND j.phone_number = f.phone_number
      WHERE f.customer_name IS NULL;

    • sujit gupta

      Thank u so much…. u have no idea how badly i needed it.
      Can u tell me what else does not run on Mysql 5.5??

    • chandu

      Minus is not working .#1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘MINUS (SELECT BloodGroup FROM `donor`)’ at line 2 why?????

  3. surendra

    Can you comment about the performance aspects of Left outer join. How do you compare the performance of inner join and left outer join.

    • Gokhan Atil

      Surendra, “left outer join” means that the result of the join will have all rows of the first table even the join-condition does not find any matching row in the second table. So it usually means “more rows” to be joined and returned. This is why its performance is worse than an inner join.

      On the other hand, you do not choose left/right/inner joins according to their performance. You use them according to your “business needs”.

  4. hi gokhan your example about minus and intersect my was util but i should one more question similar to except in mysql, i hope you read me

    thank you

    • Gokhan Atil

      Hi Raul,

      As I see, “except” works same as “minus”. What exactly do you need?

  5. Amis

    I have a table name ‘Purchase’  having a field ‘date’.Actually I want to generate a Select options for fiscal years like 2012-2013,2013-2014.

    Am extracting the unique years from the purchase table.I have my logic ready.I tried this query  ” SELECT DISTINCT(YEAR(`date`)) FROM `purchase` where  `date`<=CONCAT(year(`date`),”-03-31″)  UNION SELECT DISTINCT(YEAR(`date`)) FROM `purchase` where `date`>=CONCAT(year(`date`),”-04-01″)”

    I want to get the common result from both the queries ie.Using Intersect in place of UNION and that ll solve my problem but some how I can’t get it work.I tried even joins

    “SELECT DISTINCT(YEAR(W2.date)) FROM `purchase` as w1 INNER JOIN `purchase` as w2 ON w1.id=w2.id where w1.date<=CONCAT(year(w1.date),”-03-31″) OR w2.date>=CONCAT(year(w2.date),”-04-01″)”

    Suppose part 1 of Union query returns (2014,2013,2012) and part 2 returns(2013,2012,2011).So i want the result to be (2013,2012)ie.intersect.

     

  6. Amis

    Any ways I got it working…

    “SELECT A.years FROM (SELECT DISTINCT(YEAR(`date`))as years FROM `purchase` where `date`<=CONCAT(year(`date`),”-03-31″))as A JOIN (SELECT DISTINCT(YEAR(`date`)) as years FROM `purchase` where `date`>=CONCAT(year(`date`),”-04-01″)) as B ON A.years=B.years  ORDER BY `A`.`years` DESC”

    If you have a better solution it’ll be always appreciated :))

  7. Guillermo Malagón

    Hello, man i really want to use EXCEPT and INTERSECT

    i have mysql 5.0 BUT IT DOESN´T WORKS �

    ————————-

    SELECT horarios.id FROM horarios WHERE id_rueda = 1
    EXCEPT
    SELECT horarios.id FROM horarios WHERE id < 3

     

    RESULT OR MESSAGE:

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘EXCEPT SELECT horarios.id FROM horarios WHERE id < 3 LIMIT 0, 30’ at line 2

  8. Guillermo Malagón

    ok!! i found the answer!! MYSQL doesnt have INTERSECT

    so…

    SELECT *
    FROM horarios
    WHERE id_rueda = 1 AND (id) NOT IN
    (
    SELECT id FROM horarios WHERE id < 3
    )

  9. Lefteris

    how could the following query be perfomed in mySql?

    select sID from Apply where major = ‘CS’
    intersect
    select sID from Apply where major = ‘EE’

    thanks in advance!

  10. Dav

    another method for minus and intersect queries can be achieved using a union with flags.

    a minus query looks like this:

    intersect example:

    These should be more efficient than outer joins and ‘not in’s
    ( though I have not done any real testing… ) plus the intersect can be expanded to an arbitrary number of tables simply by adding more flags. These can also be customized to achieve interesting results by using the flags in different relationships.

    • custom visual editor seems to not work (I had this issue recently, solved it by cycling plugin activation)

      Here are the templates I use for same table MINUS and INTERSECT queries (I do use another language generally to structure the queries as it’s a PITA to write)


      /* MINUS */
      SELECT $columns
      FROM $source
      WHERE
      (
      id IN ( SELECT id FROM $source WHERE $positive_conditions )
      AND
      id NOT IN ( SELECT id FROM $source WHERE $negative_conditions )
      );

      /* INTERSECT */
      SELECT $columns
      FROM $source
      WHERE
      (
      id IN ( SELECT id FROM $source WHERE $conditions )
      AND
      id IN ( SELECT id FROM $source WHERE $other_conditions )
      );

  11. Pingback: How to sum $row

  12. JAM

    SELECT A.* , B.LOGINID CREATEDBYLOGIN,  C.LOGINID UPDATEDBYLOGIN
    FROM ROLE A,SYSUSER B, SYSUSER C
    WHERE A.CREATEDBY=B.USERID AND A.UPDATEDBY=C.USERID AND
    A.ROLEID IN ( SELECT ROLEID FROM USER_ROLE_MAP
    WHERE USERID=? AND UPPER(GRANTABLE)=’YES’
    MINUS
    SELECT ROLEID FROM USER_ROLE_MAP WHERE USERID=? ) AND upper(A.STATUS)=’ACTIVE’ order by A.ROLENAME;

    How to convert this querry in mysql, advance thanks

  13. Happy

    For Minus operator Can I write as below.

    Select * from Table1

    minus

    Select * from Table2

  14. vs

    Can the below query be re-written without MINUS
    and fetching the same result set…

    (SELECT cep.emp_id
    FROM col_emp_party cep,
    col_parties cp
    WHERE cep.emp_id = cp.empid
    AND cep.status    = ‘A’
    MINUS
    SELECT cep.emp_id
    FROM col_emp_party cep,
    col_parties cp
    WHERE cep.emp_id = cp.empid
    AND cep.status          = ‘A’
    AND ( cep.emp_cond_code   IN (‘HIGH’,’LOW’,’AVERAGE’,’POOR’)
    AND cep.effective_start_doj <= :datebind
    AND cep.effective_end_doj    > :datebind )
    );

    • Gokhan Atil

      Why do you need to write it without minus (if you use Oracle)?

  15. Pingback: SQL Reminder / Main Knowledge – Notes

  16. claudio peña

    tanks for your help

    SELECT distinct branch.city
    FROM branch JOIN propertyforrent
    ON branch.city = propertyforrent.city;

  17. Pranita

    Hi Gokhan,

    It’s very useful and it works as expected.

    Thanks..Keep it up..!!

  18. MYSQL has 2 tables. Same dates total. Compare and subtract dates.

    ​tablo1 tablo2
    ————————– ——————————
    fiyat1 tarih1 fiyat2 tarih2
    ———- ———— ———– ———–
    1200 03-2017 2100 03-2017
    1050 03-2017 5200 03-2017
    3250 04-2017 3200 04-2017
    2501 04-2017
    6100 05-2017
    1100 05-2017
    ——————————————————————

    Collecting the same dates at price 1, collecting the same dates at price 2,
    subtract 2 totals, group by date.

    I want to print something like this:

    ———————–

    05-2017 > 7200
    04-2017 > 2511
    03-2017 > -5050

    The question is true, but the result is wrong. I tried this.

    SELECT tablo1.tarih1,
    tablo1.fiyat1,
    SUM(tablo1.fiyat1),
    tablo2.tarih2,
    tablo2.fiyat2,
    SUM(tablo1.fiyat1),
    (SUM(tablo1.fiyat1) – SUM(tablo2.fiyat2)) AS sonuc
    FROM tablo1 INNER JOIN
    tablo2 ON tablo1.tarih1 = tablo2.tarih2
    GROUP BY tablo1.tarih1

  19. Diksha

    SELECT Fname, Lname
    FROM ARIEMPLOYEE2
    WHERE NOT EXISTS((SELECT PNum
    FROM ARIPROJECT2
    WHERE DNum = 5)
    minus
    (SELECT PNo
    FROM ARIWORKS_ON2
    WHERE WSSN = SSN));

    How to write this query in mysql

  20. anamadhey

    I have a table prospect

    Prospect ID country
    Smith India
    Gracy India
    Gracy Britain

    I wrote query
    Select * from prospect
    where country = india or britain
    outer join
    select * from prospect
    where country = india

    When i run query only prospect ID gracy is selected But when a prospect record has only india like prospect id smith that also has to be selected
    in simple words if a prospect has more than 1 country the required country record has to be selected otherwise default to home country ie india

Leave Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.